
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CustomerInserted_dyncrm'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER dbo.CustomerInserted_dyncrm ON dbo.Customer AFTER INSERT,UPDATE AS SELECT NULL'
GO

ALTER TRIGGER dbo.CustomerInserted_dyncrm 
   ON  dbo.Customer 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	--Looking for email inserts only
	MERGE dbo.DynamicsCrmEventQueue AS target
	USING (SELECT inserted.Id, 'EMAIL', 'I', CURRENT_TIMESTAMP FROM inserted WHERE NOT Username IS NULL) AS source (EntityId, EntityType, EventType, CreatedOnUtc)		
	ON (target.EntityId = source.EntityId AND target.EntityType = source.EntityType)
	
	WHEN NOT MATCHED THEN	
    INSERT (EntityId, EntityType, EventType, CreatedOnUtc)
    VALUES (source.EntityId, source.EntityType, source.EventType, source.CreatedOnUtc);
    
END
GO


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CustomerUpdated_dyncrm'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER dbo.CustomerUpdated_dyncrm ON dbo.Customer AFTER INSERT,UPDATE AS SELECT NULL'
GO

ALTER TRIGGER dbo.CustomerUpdated_dyncrm 
   ON  dbo.Customer 
   AFTER UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	MERGE dbo.DynamicsCrmEventQueue AS target
	--Looking for email updates only, stores old email in EventDetail
	USING (SELECT inserted.Id, 'EMAIL', 'U', deleted.Email, CURRENT_TIMESTAMP FROM inserted JOIN deleted ON inserted.Id = deleted.Id AND inserted.Email <> deleted.Email) AS source (EntityId, EntityType, EventType, EventDetail, CreatedOnUtc)		
	ON (target.EntityId = source.EntityId AND target.EntityType = source.EntityType)
	
	WHEN MATCHED 
	THEN UPDATE SET target.CreatedOnUtc = GETDATE()
	
	WHEN NOT MATCHED THEN	
    INSERT (EntityId, EntityType, EventType, EventDetail, CreatedOnUtc)
    VALUES (source.EntityId, source.EntityType, source.EventType, source.EventDetail, source.CreatedOnUtc);

	--Pour les
	--IF (SELECT COUNT(*) FROM inserted) = 0 AND (SELECT COUNT(*) FROM inserted) = 0

--	MERGE dbo.DynamicsCrmEventQueue AS target
--	USING (SELECT inserted.Id, 'CONTACT', 'U', CURRENT_TIMESTAMP FROM inserted) AS source (EntityId, EntityType, EventType, CreatedOnUtc)		
--	ON (target.EntityId = source.EntityId AND target.EntityType = source.EntityType)
	
--	WHEN MATCHED 
--	THEN UPDATE SET target.CreatedOnUtc = GETDATE()
	
--	WHEN NOT MATCHED THEN	
--    INSERT (EntityId, EntityType, EventType, CreatedOnUtc)
--    VALUES (source.EntityId, source.EntityType, '', source.CreatedOnUtc);
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.CustomerAttributeChanged_dyncrm'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER dbo.CustomerAttributeChanged_dyncrm ON dbo.CustomerAttribute AFTER INSERT,UPDATE AS SELECT NULL'
GO

ALTER TRIGGER dbo.CustomerAttributeChanged_dyncrm 
   ON  dbo.CustomerAttribute 
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--IF ( UPDATE (xxx))
	--BEGIN
		MERGE dbo.DynamicsCrmEventQueue AS target
		USING (SELECT inserted.CustomerId, 'CONTACT', CURRENT_TIMESTAMP FROM inserted WHERE [key] IN ('FirstName', 'LastName', 'Gender', 'DateOfBirth')) AS source (EntityId ,EntityType ,CreatedOnUtc)		
		ON (target.EntityId = source.EntityId AND target.EntityType = source.EntityType)
		
		WHEN MATCHED 
		THEN UPDATE SET target.CreatedOnUtc = GETDATE()
		
		WHEN NOT MATCHED THEN	
	    INSERT (EntityId, EntityType, EventType, CreatedOnUtc)
	    VALUES (source.EntityId, source.EntityType, 'U', source.CreatedOnUtc);
	    
	    
	    MERGE dbo.DynamicsCrmEventQueue AS target
		USING (SELECT inserted.CustomerId, 'ACCOUNT', CURRENT_TIMESTAMP FROM inserted WHERE [key] IN ('FirstName', 'LastName', 'Company')) AS source (EntityId ,EntityType ,CreatedOnUtc)		
		ON (target.EntityId = source.EntityId AND target.EntityType = source.EntityType)
		
		WHEN MATCHED 
		THEN UPDATE SET target.CreatedOnUtc = GETDATE()
		
		WHEN NOT MATCHED THEN	
	    INSERT (EntityId, EntityType, EventType, CreatedOnUtc)
	    VALUES (source.EntityId, source.EntityType, 'U', source.CreatedOnUtc);

	--END
END
GO



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.NewsLetterSubscriptionChanged_dyncrm'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER dbo.NewsLetterSubscriptionChanged_dyncrm ON dbo.NewsLetterSubscription AFTER INSERT,UPDATE AS SELECT NULL'
GO

ALTER TRIGGER dbo.NewsLetterSubscriptionChanged_dyncrm 
   ON  dbo.NewsLetterSubscription 
   AFTER INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--IF ( UPDATE (xxx))
	--BEGIN
		MERGE dbo.DynamicsCrmEventQueue AS target
		USING (SELECT inserted.Id, 'CONTACT', CURRENT_TIMESTAMP FROM inserted) AS source (EntityId ,EntityType ,CreatedOnUtc)		
		ON (target.EntityId = source.EntityId AND target.EntityType = source.EntityType)
		
		WHEN MATCHED 
		THEN UPDATE SET target.CreatedOnUtc = GETDATE()
		
		WHEN NOT MATCHED THEN	
	    INSERT (EntityId, EntityType, EventType, CreatedOnUtc)
	    VALUES (source.EntityId, source.EntityType, '', source.CreatedOnUtc);
	    
	--END
END
GO







